package com.rx.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.rx.common.DBUtils;
import com.rx.entity.Admin;
import com.rx.entity.Role;
/**
 * 
 * @类名: AdminDaoImpl 
 * @描述: 管理员数据访问实现类 
 * @作者：侯建军
 * @日期：2018年6月26日 下午3:18:00 
 *
 */
public class AdminDaoImpl implements AdminDaoI<Admin> {

	@Override
	public boolean insert(Admin admin) throws SQLException {
		Connection conn = DBUtils.getConnection();
		String sql = "INSERT INTO admin (admin,password,adminName,gender,mobile,email,address,roleId,deptId,createDate,lastDate,isEnable,remark) \r\n"
				+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, admin.getAdmin());
		ps.setString(2, admin.getPassword());
		ps.setString(3, admin.getAdminName());
		ps.setInt(4, admin.getGender());
		ps.setString(5, admin.getMobile());
		ps.setString(6, admin.getEmail());
		ps.setString(7, admin.getAddress());
		ps.setInt(8, admin.getRoleId());
		ps.setInt(9, admin.getDeptId());
		
		// 转换类型
		ps.setTimestamp(10, new Timestamp(new java.util.Date().getTime()));
		if (null != admin.getLastDate()) {
			ps.setDate(11, new Date(admin.getLastDate().getTime()));
		} else {
			ps.setDate(11, null);
		}
		ps.setInt(12, 0);
		ps.setString(13, admin.getRemark());
		// 执行语句
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		return false;
	}

	@Override
	public boolean update(Admin admin) throws SQLException {
		Connection conn = DBUtils.getConnection();
		String sql = "UPDATE admin SET admin=?,password=?,adminName=?,gender=?,mobile=?,email=?,address=?,roleId=?,deptId=?,createDate=?,lastDate=?,isEnable=?,remark=? WHERE id=?";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, admin.getAdmin());
		ps.setString(2, admin.getPassword());
		ps.setString(3, admin.getAdminName());
		ps.setInt(4, admin.getGender());
		ps.setString(5, admin.getMobile());
		ps.setString(6, admin.getEmail());
		ps.setString(7, admin.getAddress());
		ps.setInt(8, admin.getRoleId());
		ps.setInt(9, admin.getDeptId());
		if (null != admin.getCreateDate()) {
			// 转换类型
			ps.setDate(10, new Date(admin.getCreateDate().getTime()));
		} else {
			ps.setDate(10, null);
		}
		if (null != admin.getLastDate()) {
			ps.setDate(11, new Date(admin.getLastDate().getTime()));
		} else {
			ps.setDate(11, null);
		}
		ps.setInt(12, admin.getIsEnable());
		ps.setString(13, admin.getRemark());
		ps.setInt(14, admin.getId());
		// 执行语句
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		return false;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		Connection conn = DBUtils.getConnection();
		String sql = "DELETE FROM admin WHERE id=?";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		if (ps.executeUpdate() > 0) {
			return true;
		}
		DBUtils.close(conn, ps, null);
		return false;
	}

	@Override
	public List<Admin> selectAll() throws SQLException {
		Connection conn = DBUtils.getConnection();
		String sql = "SELECT id,admin,password,adminName,gender,mobile,email,address,roleId,deptId,createDate,lastDate,isEnable,remark FROM admin";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sql);
		//将数据库中的记录转换为对象
		List<Admin>  list=new ArrayList<>();
		//结果集
		ResultSet rs=ps.executeQuery();
		while (rs.next()) {
			Admin admin=new Admin();
			
			admin.setId(rs.getInt(1));
			admin.setAdmin(rs.getString(2));
			admin.setPassword(rs.getString(3));
			admin.setAdminName(rs.getString(4));
			admin.setGender(rs.getInt(5));
			admin.setMobile(rs.getString(6));
			admin.setEmail(rs.getString(7));
			admin.setAddress(rs.getString(8));
			admin.setRoleId(rs.getInt(9));
			admin.setDeptId(rs.getInt(10));
			admin.setCreateDate(rs.getTimestamp(11));
			admin.setLastDate(rs.getTimestamp(12));
			admin.setIsEnable(rs.getInt(13));
			admin.setRemark(rs.getString(14));
			
			list.add(admin);
		}
		DBUtils.close(conn, ps, rs);
		return list;
	}

	@Override
	public List<Admin> selectWhere(String whereSql) throws Exception {
		Connection conn = DBUtils.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT id,admin,password,adminName,gender,mobile,email,address,roleId,deptId,createDate,lastDate,isEnable,remark ");
		sb.append("FROM admin ");
		sb.append(" WHERE 1=1 ");
		sb.append(whereSql);
		//String sql = "SELECT id,admin,password,adminName,gender,mobile,email,address,roleId,deptId,createDate,lastDate,remark FROM admin";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sb.toString());
		//将数据库中的记录转换为对象
		List<Admin>  list=new ArrayList<>();
		//结果集
		ResultSet rs=ps.executeQuery();
		while (rs.next()) {
			Admin admin=new Admin();
			
			admin.setId(rs.getInt(1));
			admin.setAdmin(rs.getString(2));
			admin.setPassword(rs.getString(3));
			admin.setAdminName(rs.getString(4));
			admin.setGender(rs.getInt(5));
			admin.setMobile(rs.getString(6));
			admin.setEmail(rs.getString(7));
			admin.setAddress(rs.getString(8));
			admin.setRoleId(rs.getInt(9));
			admin.setDeptId(rs.getInt(10));
			admin.setCreateDate(rs.getTimestamp(11));
			admin.setLastDate(rs.getTimestamp(12));
			admin.setIsEnable(rs.getInt(13));
			admin.setRemark(rs.getString(14));
			
			list.add(admin);
		}
		DBUtils.close(conn, ps, rs);
		return list;
	}

	@Override
	public Map<String, Object> selectPage(String whereSql, int currentPage, int pageSize) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<String,Object> findAdminAndRole(String whereSql, int currentPage, int pageSize) throws Exception {
		Connection conn = DBUtils.getConnection();
		int index = (currentPage - 1) * pageSize;
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT admin.id,admin,password,adminName,gender,mobile,email,address,roleName,roleId,deptId,createDate,lastDate,isEnable,admin.remark ");
		sb.append("FROM admin,role WHERE admin.roleId=role.id ");
		sb.append(whereSql);
		sb.append(" ORDER BY admin.id DESC ");
		sb.append(" LIMIT "+index+","+pageSize);
		//String sql = "SELECT id,admin,password,adminName,gender,mobile,email,address,roleId,deptId,createDate,lastDate,remark FROM admin";
		// 创建PreparedStatement
		PreparedStatement ps = conn.prepareStatement(sb.toString());
		//将数据库中的记录转换为对象
		List<Admin>  list=new ArrayList<>();
		//结果集
		ResultSet rs=ps.executeQuery();
		while (rs.next()) {
			Admin admin=new Admin();
			
			admin.setId(rs.getInt(1));
			admin.setAdmin(rs.getString(2));
			admin.setPassword(rs.getString(3));
			admin.setAdminName(rs.getString(4));
			admin.setGender(rs.getInt(5));
			admin.setMobile(rs.getString(6));
			admin.setEmail(rs.getString(7));
			admin.setAddress(rs.getString(8));
			//创建角色对象
			Role role=new Role();
			role.setRoleName(rs.getString(9));
			role.setId(rs.getInt(10));
			//添加角色
			admin.setRole(role);
			admin.setDeptId(rs.getInt(11));
			admin.setCreateDate(rs.getTimestamp(12));
			admin.setLastDate(rs.getTimestamp(13));
			admin.setIsEnable(rs.getInt(14));
			admin.setRemark(rs.getString(15));
			
			list.add(admin);
		}
		
		String sql="SELECT count(*) FROM admin,role WHERE admin.roleId=role.id ";
		PreparedStatement ps1 = conn.prepareStatement(sql);
		ResultSet rs1=ps1.executeQuery();
		rs1.next();
		Integer count=rs1.getInt(1);
		
		Map<String,Object> map=new HashMap<>();
		int pageNum=1;
		if(count%pageSize==0) {
			pageNum=count/pageSize;
		}else {
			pageNum=count/pageSize+1;
		}
		map.put("list", list);
		map.put("count", count);
		map.put("pageNum", pageNum);
		DBUtils.close(conn, ps, rs);
		return map;
	}
}
